package com.cloud.dao.bookkeeping;

import com.cloud.model.entity.bookkeeping.Bookkeeping;
import com.cloud.model.view.comsumepie.ComsumePieView;
import com.cloud.model.view.consumetype.ConsumeTypeView;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * <b><code>BookkeepingDao</code></b>
 * <p/>
 * Bookkeeping的具体实现
 * <p/>
 * <b>Creation Time:</b> 2019-10-15 17:24:19.
 *
 * @author ${model.author}
 * @version 1.0.0
 * @since cloud-spring-boot-be 1.0.0
 */
@Repository
public interface BookkeepingDao extends JpaRepository<Bookkeeping, Long>, JpaSpecificationExecutor<Bookkeeping> {

    @Query(value = "select sum(if(tct.type = 0,tb.money,0)) as expenditure,sum(if(tct.type = 1,tb.money,0)) as income from t_bookkeeping as tb\n" +
            "left join t_consume_type tct on tb.consume_type_id = tct.id where tb.user_id = ?1 and tb.consume_time between ?2 and ?3", nativeQuery = true)
    Map<String,Long> sunByUserIdAndConsumeTimeBetween(Long userId, Long startTime, Long endTime);

    Page<Bookkeeping> findAllByUserIdAndEnabledOrderByConsumeTimeDesc(Long userId, Integer enabled, Pageable pageable);

    @Query(value = "select sum(money) from t_bookkeeping where enabled = ?1 and user_id = ?2 and  consume_time between ?3 and ?4",nativeQuery = true)
    Integer sunByEnabledAndUserIdAndConsumeTimeBetween(Integer enabled, Long userId, Long dayStartTime, Long dayEndTime);

    @Modifying
    @Transactional
    @Query(value = "update t_bookkeeping set is_show_time = 0,update_time = ?1 where user_id= ?2 and is_show_time = ?3 and consume_time between ?4 and ?5",nativeQuery = true)
    void updateIsShowTimeByUserIdAndIsShowTimeAndConsumeTimeBetween(Long currentTimeMillis,Long userId, Integer enabled, Long currentStartTime, Long currentEndTime);

    List<Bookkeeping> findByUserIdAndIsShowTimeAndConsumeTimeBetweenOrderByWeightDesc(Long userId, Integer isShowTime, Long currentStartTime, Long currentEndTime);

    @Modifying
    @Transactional
    @Query(value = "update t_bookkeeping set is_show_time = ?1,update_time = ?2 where id= ?3",nativeQuery = true)
    void updateIsShowTimeById(Integer enabled, long currentTimeMillis, Long id);

    @Query(value = "select new com.cloud.model.view.comsumepie.ComsumePieView(SUM(tb.money),tct.name) from Bookkeeping as tb " +
            "left join ConsumeType tct on tb.consumeTypeId = tct.id " +
            "where tb.userId = ?1 and tct.type = ?4 and tb.consumeTime between ?2 and ?3 " +
            "group by tct.id ")
    List<ComsumePieView> countMoneyByConsumeTimeAndUserId(Long userId, Long startTimeValue, Long endTimeValue, Integer type);

    @Query(value = "select consume_time from t_bookkeeping where user_id = ?1 and enabled = ?2 order by consume_time desc limit 0 ,1;",nativeQuery = true)
    Long findOneByUserIdAndEnabledOrderByConsumeTimeDesc(Long userId, Integer enabled);

    @Query(value = "select consume_time from t_bookkeeping where user_id = ?1 and enabled = ?2 order by consume_time asc limit 0 ,1;",nativeQuery = true)
    Long findOneByUserIdAndEnabledOrderByConsumeTimeAsc(Long userId, Integer enabled);
}
